﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using KPIS.GERP.FileImportToDb.Models;
using KPIS.GERP.Models;
using KPIS.GERP.Models.Class;
using KPIS.GERP.Models.FIM;
using KPIS.GERP.Models.ICM;
using OfficeOpenXml;
using OfficeOpenXml.Table;
using System.Data.Entity;

namespace KPIS.GERP.FileImportToDb.Controllers
{
    public class MasContactController : Controller
    {
        public ActionResult Index()
        {
            FileInfo file = new FileInfo(@"D:\beneficiary_250457.xlsx");

            var data = new List<beneficiary>();
            using (ExcelPackage pck = new ExcelPackage(file, true))
            {
                var workSheet1 = pck.Workbook.Worksheets["beneficiary"];
                int rowEnd = workSheet1.Dimension.End.Row;
                int rowStart = 2;
                for (int i = rowStart; i <= rowEnd; i++)
                {
                    int j = 2;
                    var eachRow = new beneficiary
                    {
                        RowIndex = i,
                        nickname = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        fullname = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        account = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        address1 = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        address2 = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        email = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        tel = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        fax = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        tax = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        sms = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        idcard = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        document = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        bank_Code = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        bank_Name = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        branch_code = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        company_id = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        department_id = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        ref1 = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        ref2 = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        created_by = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        updated_by = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        created_date = Convert.ToString(workSheet1.Cells[i, j++].Value),
                        updated_date = Convert.ToString(workSheet1.Cells[i, j++].Value),
                    };

                    data.Add(eachRow);
                }
            }

            var db = new GERPContext(true);
            var contacts = db.MasContacts.Where(o => o.RecordStatus != "D").ToList();
            var contactBanks = db.MasContactBanks.Include(o => o.MasBankBranch).Where(o => o.RecordStatus != "D").ToList();
            var bankBranchs = db.MasBankBranchs.Where(o => o.RecordStatus != "D").ToList();
            var banks = db.MasBanks.Where(o => o.RecordStatus != "D").ToList(); 
            List<VM1> result = new List<VM1>();
            foreach (var d in data)
            {
                var vm1 = new VM1();
                vm1.Beneficiary = d;
                vm1.FindContactInCurrentDb(contacts);
                vm1.FindContactBankInCurrentDb(contactBanks);
                if (vm1.MasContact == null)
                {
                    var c = new MasContact();
                    c.NameTh = d.fullname;
                    c.ConCategory = 1;
                    c.ConGrpId = 47;
                    c.TaxCode = d.tax;
                    c.RegistedDate = ConvertUtility.ConvertOrDefaultToDateTime(d.created_date);
                    c.VatTypeId = 55;
                    c.ConStatusId = 51;
                    c.Country = "ไทย";
                    c.Building = d.address1;
                    c.Village = d.address2;
                    c.RecordStatus = "U";
                    c.CreatedBy = 1;
                    c.CreatedWhen = DateTime.Now;
                    db.MasContacts.Add(c);
                    db.SaveChanges();
                    vm1.MasContact = c;
                }

                var b = banks.FirstOrDefault(o => Convert.ToInt32(o.Code) == Convert.ToInt32(d.bank_Code));
                if (b == null)
                {
                    var newB = new MasBank();
                    newB.Code = d.bank_Code.StartsWith("0") ? d.bank_Code.Substring(1) : d.bank_Code;
                    newB.Name = d.bank_Name;
                    newB.RecordStatus = "U";
                    newB.CreatedBy = 1;
                    newB.CreatedWhen = DateTime.Now;
                    db.MasBanks.Add(newB);
                    db.SaveChanges();
                    b = newB;
                    banks = db.MasBanks.Where(o => o.RecordStatus != "D").ToList();
                }
                var bb = bankBranchs.FirstOrDefault(o =>
                    Convert.ToInt32(o.Code) == Convert.ToInt32(d.branch_code) &&
                    Convert.ToInt32(o.Bank.Code) == Convert.ToInt32(d.bank_Code));

                if (bb == null)
                {
                    var newBb = new MasBankBranch();
                    newBb.Code = d.branch_code.StartsWith("0") ? d.branch_code.Substring(1) : d.branch_code;
                    newBb.BranchName = d.bank_Name + " สาขา " + d.branch_code;
                    newBb.BankId = b.BankId;
                    newBb.RecordStatus = "U";
                    newBb.CreatedBy = 1;
                    newBb.CreatedWhen = DateTime.Now;
                    db.MasBankBranchs.Add(newBb);
                    db.SaveChanges();
                    bb = newBb;
                    bankBranchs = db.MasBankBranchs.Where(o => o.RecordStatus != "D").ToList();
                }
                
                if (vm1.MasContactBank == null)
                {
                    var cb = new MasContactBank();
                    cb.ConId = vm1.MasContact.ConId;
                    cb.BranchId = bb.BranchId;
                    cb.AccountCode = d.account;
                    cb.AccountName = d.fullname;
                    cb.RecordStatus = "U";
                    cb.CreatedBy = 1;
                    cb.CreatedWhen = DateTime.Now;
                    db.MasContactBanks.Add(cb);
                }
                result.Add(vm1);
            }
            db.SaveChanges();
            return View(result);
        }
    }
}